using System;
using System.Data.SqlClient;
using GeoAPI.Geometries;
using Npgsql;
using System.Collections.Generic;

namespace MsSqlSpatial.Utility
{
	public class ImportPostGis: StoredProcedures.IDataImportProcess
	{
		private ConnectionOptions sqlServerConnection;
		private ConnectionOptions postGisConnection;
		private SqlServerOptions sqlServerOptions;
		private PostGisOptions postGisOptions;
		private bool appendRows;

		private NpgsqlConnection connection;
		private NpgsqlCommand command;
		private NpgsqlDataReader reader;

		private Dictionary<string, Type> columns;

		internal ImportPostGis(
			ConnectionOptions sqlServerConnection,
			ConnectionOptions postGisConnection,
			SqlServerOptions sqlServerOptions,
			PostGisOptions postGisOptions,
			bool appendRows)
		{

			this.sqlServerConnection = sqlServerConnection;
			this.postGisConnection = postGisConnection;
			this.sqlServerOptions = sqlServerOptions;
			this.postGisOptions = postGisOptions;
			this.appendRows = appendRows;

			if (string.IsNullOrEmpty(postGisOptions.Schema))
				postGisOptions.Schema = "public";

			string sql = string.Format("SELECT *, AsBinary({0}) AS MSS_WKB_GEOMETRY FROM {1}", 
				postGisOptions.GeometryColumn, 
				postGisOptions.Schema + "." + postGisOptions.Table);

			connection = new NpgsqlConnection(postGisConnection.GetPostGisConnectionString());
			command = new NpgsqlCommand(sql, connection);
			connection.Open();
			reader = command.ExecuteReader();
		}


		public void Dispose()
		{
			if (reader != null)
				reader.Dispose();
			if (command != null)
				command.Dispose();
			if (connection != null)
				connection.Dispose();
		}

		internal TaskStats Execute()
		{
			TaskStats result;

			StoredProcedures.DataImportProcess(
				this,
				sqlServerOptions.Schema,
				sqlServerOptions.Table,
				sqlServerOptions.Column,
				sqlServerOptions.SRID,
				sqlServerOptions.Subtype,
				sqlServerConnection.GetSqlServerConnectionString(),
				this.appendRows,
				out result.RowsProcessed,
				out result.ElapsedTime);

			return result;
		}


		Dictionary<string,Type>  StoredProcedures.IDataImportProcess.GetColumns()
		{
			return GetColumns();
		}

		private Dictionary<string, Type> GetColumns()
		{
			if (columns == null)
			{
				columns = new Dictionary<string, Type>();
				for (int i = 0; i < reader.FieldCount - 1; i++)
				{
					string name = reader.GetName(i);
					if (name != postGisOptions.GeometryColumn && reader.GetDataTypeName(i) != "geometry")
						columns[name] = reader.GetFieldType(i);
				}
			}
			return columns;
		}

		void  StoredProcedures.IDataImportProcess.Execute(SqlCommand command, string columnName, int srid, out long importedRows)
		{
			importedRows = 0;
			Dictionary<string, Type> columns = this.GetColumns();

			while (reader.Read())
			{
				IGeometry geometry = ConvertGeometry.ToGeometryFromWKB((byte[])reader.GetValue(reader.GetOrdinal("MSS_WKB_GEOMETRY")));

				// Set values
				foreach (string column in columns.Keys)
				{
					command.Parameters["@" + column].Value = reader.GetValue(reader.GetOrdinal(column));
				}
				command.Parameters["@" + columnName].Value = ConvertGeometry.ToSqlBytes(geometry, srid).Value;

				// Insert row
				command.ExecuteNonQuery();
				importedRows++;
			}
		}

	}
}
